from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Table,MetaData,Integer,String,Date,Boolean
#创建数据库连接对象，同时指出字符集，最大连接数
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/mydb?charset=utf8',max_overflow=5)
#创建一个基类，用来生成指定的表
Base = declarative_base()


class Students(Base):
    #表名
    __tablename__ = 'students'
    #主键
    id = Column(Integer,primary_key=True,autoincrement=True)#设置主键自增
    name = Column(String(20))
    age = Column(Integer)
    birthday = Column(Date)

    def __repr__(self):
        return self.id+' '+self.name


class Person(Base):
    __tablename__='person'
    id = Column(String(5),primary_key=True,autoincrement=False)
    name = Column(String(20),nullable=False)
    sex = Column(Boolean)


def init_db():
    # Base.metadata.create_all(engine)
    # Base.metadata.create(engine,Students)
    meta = MetaData()#此处与Base.metadata等价，都是创建一个Metadata（元数据）对象
    person = Table('persons',meta,
                   Column('id',String(5),primary_key=True,autoincrement=False),
                   Column('name',String(20),nullable=False),
                   Column('sex',Boolean)
                   )
    person.create(bind=engine)


def drop_db():
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    init_db()

# cur = engine.execute("insert into person values(10006,'rose',20,'南京')")
